Parsing JSON

Some types of data in UniFi are stored as JavaScript Object Notation or JSON. This may be the case for data that comes from reference or external dropdowns and some types of user data. You can tell where this is the case as rather than have a single item of data, there will be a number of pair of names and values enclosed in curly brackets, so it will display something like this:

{"supplier_code":"C001","supplier_name":"Example Supplier"}

To get the individual values into a report we can use the custom column feature with the ::json->> function. This uses the format:

<customer_account>.<app_id>.<field_id>::json->>'JSON_item_name'

So if the above JSON example was in the 'demo' account of the 'Purchase Invoice' app 'Supplier' field, the custom column to extract just the supplier name would look like:

demo.purchase_invoice.supplier::json->>'supplier_name'

The easiest way to build this formula is to drag the field containing the JSON into the report and fetch the data. This will show the data in JSON format so you can see the name of the field you want to extract. Then for that field select the 'Change to Aggregate Column' > 'Custom Expression' option. This will complete the first part of the expression above, you can then add the following to the end of this expression:

::json->>'item_name'

Change item_name to the item you want to retrieve and press enter. You can now fetch the data to test your formula. You can also change the column header to something more meaningful than 'Custom SQL Column' by clicking on the title in the fields section and amending.